package dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import util.C3P0Util;
import domain.Owner;
import domain.Pet;

public class OwnerDaoImpl {
	
	/**
	 * 查看所有宠物主人信息
	 * @return
	 * @throws SQLException
	 */
	public List<Owner> findAllOwners() throws SQLException{
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		return qr.query("select * from owner", new BeanListHandler<Owner>(Owner.class));
	}
	/**
	 * 添加主人信息
	 * @param owner
	 * @throws SQLException
	 */
	public void addOwner(Owner owner) throws SQLException{
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		qr.update("INSERT INTO owner VALUES(?,?,?,?,?)",owner.getOwnerId(),owner.getOwnerName(),owner.getOwnerAddress(),owner.getOwnerCity(),owner.getOwnerPhone());
	}
	
	/**
	 * 通过ownerId找到主人信息
	 * @param ownerId
	 * @return 
	 * @throws SQLException 
	 */
	public Owner findOwnerById(String ownerId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		return qr.query("select * from owner where ownerId=?", new BeanHandler<Owner>(Owner.class),ownerId);
	}
	
	/**
	 * 更新主人信息
	 * @param owner
	 * @throws SQLException
	 */
	public void updateOwner(Owner owner) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		qr.update("update owner set ownerName=?,ownerAddress=?,ownerCity=?,ownerPhone=? where ownerId=?",
				owner.getOwnerName(),owner.getOwnerAddress(),owner.getOwnerCity(),owner.getOwnerPhone(),owner.getOwnerId());
	}
	/**
	 * 查找指定医生负责的所有宠物
	 * @param ownerId
	 * @return
	 * @throws SQLException
	 */
	public List<Pet> findPetsByOwnerId(String ownerId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		return qr.query("select * from pet where ownerId=?",new BeanListHandler<Pet>(Pet.class),ownerId );
	}
	/**
	 * 通过ownerId删除主人信息
	 * @param doctorId
	 * @throws SQLException
	 */
	public void deleteOwnerById(String ownerId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		qr.update("delete from owner where ownerId=?",ownerId );
	}
	/**
	 * 
	 * @param ownerId
	 * @param ownerName
	 * @param ownerAddress
	 * @param ownerCity
	 * @param ownerPhone
	 * @return
	 */
	public List<Owner> findOwnerByConditions(String ownerId, String ownerName,
			String ownerAddress, String ownerCity, String ownerPhone) throws SQLException{
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select * from owner where 1=1";
		List list = new ArrayList();
		if(!"".equals(ownerId.trim())){
			sql+=" and ownerId like ?"; //  不能在这写%   %'1002'%
			list.add("%"+ownerId.trim()+"%");// '%1002%'
		}
		
		if(!"".equals(ownerName.trim())){
			sql+=" and ownerName like ?";
			list.add("%"+ownerName.trim()+"%");
		}
		
		if(!"".equals(ownerAddress.trim())){
			sql+=" and ownerAddress like ?";
			list.add("%"+ownerAddress.trim()+"%");
		}
		
		if(!"".equals(ownerCity.trim())){
			sql+=" and ownerCity like ?";
			list.add("%"+ownerCity.trim()+"%");
		}
		if(!"".equals(ownerPhone.trim())){
			sql+=" and ownerPhone like ?";
			list.add("%"+ownerPhone.trim()+"%");
		}
		
		return qr.query(sql, new BeanListHandler<Owner>(Owner.class),list.toArray());
	}
}
